package com.javaee.term.mapper;

import com.javaee.term.data.ConverAnaly;
import com.javaee.term.data.TurnOver;
import com.javaee.term.model.SaleAdmin;
import com.javaee.term.model.SaleMan;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.BaseMapper;

import java.util.List;

public interface SaleManMapper extends BaseMapper<SaleMan> {

    @Select("SELECT sale_man.name label, SUM(contact.contact_money) y \n" +
            "FROM `sale_man` LEFT JOIN contact on contact.man_id = sale_man.man_id\n" +
            "GROUP BY sale_man.man_id\n" +
            "ORDER BY y DESC\n" )
    List<TurnOver>  getAllSalesmanTurnOver();

    /**
     * 查询某员工每年业绩
     * @param id
     * @return
     */
    @Select("SELECT YEAR(contact.create_time) label, SUM(contact.contact_money) y\n" +
            "FROM `sale_man` LEFT JOIN contact on contact.man_id = sale_man.man_id\n" +
            "WHERE sale_man.man_id = #{id} \n" +
            "GROUP BY YEAR(contact.create_time)")
    List<TurnOver>   getTheSumEachYear(int id);
    
    /**
     * 查询某员工每月业绩
     * @param id
     * @return
     */
    @Select("SELECT MONTH(contact.create_time) label, SUM(contact.contact_money) y\n" +
            "FROM `sale_man` LEFT JOIN contact on contact.man_id = sale_man.man_id\n" +
            "WHERE sale_man.man_id = #{id} AND YEAR(contact.create_time) = #{year}\n" +
            "GROUP BY MONTH(contact.create_time)")
    List<TurnOver>   getTheSumEachMouth(String year,int id);

    @Select("select * from sale_man where account= #{account }")
    SaleMan findByAccount(String account);

}